<?php

    // Created By : Noan Babao
    // B.S. Computer Science - De La Salle Canlubang
    // October 27,2011
    //NOT IN parameters : documentType , documentName documentId , location
    //NOT OUT parameters : List of Attachments.

// Start - Checker for those users who will just go to the page by typing directly in the url.
if ($_POST) {
    // INCLUDES CONFIGURATION VARIABLES FOR DATABASE ACCESS
 $hostName = "localhost";
 $rootName = "dbasegr";
 $dBasePassword = "dbgr2012";
 $dBaseName = 'greenroute';

//    // CONNECT TO DATABASE
    $connect = mysql_connect($hostName,$rootName,$dBasePassword) or die ('Unable to connect!');
    mysql_select_db($dBaseName) or die ('Unable to select database!');

$docName = $_POST['documentName'];
$docId = $_POST['documentId'];
$location =  $_POST['location'];
$documentTags =  explode(",",$_POST['documentTags']);
$departmentId = $_POST['departmentId'];
$positionId = $_POST['positionId'];
date_default_timezone_set('Asia/Singapore');
$currentDateTime= date("Y-m-d H:i:s" , time());

//Query Insert
    $queryAttachment = 
            "
                INSERT into Attachment 
                values('$docId', NULL , '$docName', '$location' , 'Active')
            ";
    
// Query Tagged Log    
    $queryLog = 
            "
                INSERT into Log (documentId, userId, stepId, logDateTime, activity)
                values('$docId' , '$location' , NULL , '$currentDateTime' , 'Tagged');
            ";


// get deptPosId;    
 $deptPosId = "";
 $query =
            "
                SELECT DP.deptPosId as deptPosId, D.departmentName as departmentName, P.positionName as positionName
                FROM Department_Position AS DP, Department as D, Position as P
                WHERE DP.departmentId = D.departmentId
                    AND DP.positionId = P.positionId
                    AND DP.departmentId = '$departmentId' AND DP.positionId = '$positionId'
            ";

        $result = mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
        $row = mysql_fetch_array($result);
        $deptPosId = $row['deptPosId'];
        $departmentName = $row['departmentName'];
        $positionName = $row['positionName'];

// Insert data in Sent_Attachment
        $querySent_Attachment =
            "
                INSERT INTO Sent_Attachment (documentId, senderId, receiverId, receiveFlag, orgFlag) VALUES
                    ('$docId', '$location', '$deptPosId', 'Pending', 'Internal');
            ";

        $queryLogSent =
            "
                INSERT INTO Log (documentId, userId, stepId, logDateTime, activity) VALUES
                    ('$docId', '$location', NULL , '$currentDateTime', 'Sent');
            ";
    
        
// check labels
        $getMaxLabel = "
                        SELECT Count(*) as maxLabel
                        FROM Label

                   ";
    $result = mysql_query($getMaxLabel) or die ('Error in query: $getMaxLabel. ' . mysql_error());
    if (mysql_num_rows($result) > 0){
        while ($row = mysql_fetch_array($result)) {

            $maxLabel = $row['maxLabel'];

        }
    }
   //add main document label!
    
    $queryExistingLabel = "
                SELECT *
                FROM Label
                WHERE label like '%$documentTags[0]%'
            ";
 
$result = mysql_query($queryExistingLabel) or die ('Error in query: $queryExistingLabel. ' . mysql_error());




if (mysql_num_rows($result) > 0){
    while ($row = mysql_fetch_array($result)) {

        $labelId = $row['labelId'];

    }
    $addDocumentLabel = "
                            INSERT INTO Document_Label
                            values('$docId' , '$labelId', '1');
                        ";
    mysql_query($addDocumentLabel) or die ('Error in query: $addDocumentLabel. ' . mysql_error());
    
}

else {
     $maxLabel++;
     $queryAddLabel ="
                        INSERT INTO Label
                        values('$maxLabel','$documentTags[0]')
                     ";

     mysql_query($queryAddLabel) or die ('Error in query: $queryAddLabel. ' . mysql_error());

     $queryAddDocumentLabel =   "
                                    INSERT INTO Document_Label
                                    values('$docId','$maxLabel','1');
                                ";
     mysql_query($queryAddDocumentLabel) or die ('Error in query: $queryAddDocumentLabel. ' . mysql_error());   
}


for($x=1; $x< count($documentTags) ;$x++)
{
    $queryExistingLabel = "
                    SELECT *
                    FROM Label
                    WHERE label like '%$documentTags[$x]%'
                ";

    $result = mysql_query($queryExistingLabel) or die ('Error in query: $queryExistingLabel. ' . mysql_error());




    if (mysql_num_rows($result) > 0){
        while ($row = mysql_fetch_array($result)) {

            $labelId = $row['labelId'];

        }
        $addDocumentLabel = "
                                INSERT INTO Document_Label
                                values('$docId' , '$labelId', '0');
                            ";
        mysql_query($addDocumentLabel) or die ('Error in query: $addDocumentLabel. ' . mysql_error());

    }

    else {
         $maxLabel++;
         $queryAddLabel ="
                            INSERT INTO Label
                            values('$maxLabel','$documentTags[$x]')
                         ";

         mysql_query($queryAddLabel) or die ('Error in query: $queryAddLabel. ' . mysql_error());

         $queryAddDocumentLabel =   "
                                        INSERT INTO Document_Label
                                        values('$docId','$maxLabel','0');
                                    ";
         mysql_query($queryAddDocumentLabel) or die ('Error in query: $queryAddDocumentLabel. ' . mysql_error());   
    }
}


            
    
    mysql_query($queryAttachment) or die ('Error in query: $queryAttachment. ' . mysql_error());
    mysql_query($querySent_Attachment) or die ('Error in query: $query. ' . mysql_error());
    mysql_query($queryLog) or die ('Error in query: $queryLog. ' . mysql_error());
    mysql_query($queryLogSent) or die ('Error in query: $query. ' . mysql_error());
    
    echo json_encode(1);
    
    mysql_close($connect);
}   // End - Checker for those users who will just go to the page by typing directly in the url.
else {
 echo "You are not authorized to view this page. This incident will be reported immediately.";
} 
?>
